<?php
/**
 * Created by PhpStorm.
 * User: 沁塵
 * Date: 2019/4/20
 * Time: 19:57
 */

namespace app\api\controller\v1;
require_once '../vendor/PHPExcel/Classes/PHPExcel/IOFactory.php';
require_once '../vendor/PHPExcel/Classes/PHPExcel/Reader/Excel5.php';
use app\api\model\Account;
use app\api\model\ProductMap;
use app\api\model\User;
use app\api\service\token\LoginToken;
use app\api\model\SnycFromGuang;
use app\api\model\SnycDistribute;
use app\lib\exception\sync\syncException;
use app\lib\yiyan\yiyan;
use PHPExcel;
use PHPExcel_IOFactory;
use think\Db;
use think\Request;

class Sync
{
    /**
     * 逛逛插件生成的内容同步到京东素材表
     * @param Request $request
     * @return \think\response\Json
     */
    public function create(Request $request)
    {
        $params = $request->post();

        $flag = SnycFromGuang::where('unique_id',$params['unique_id'])->find();

        if($flag){
            throw new syncException(['msg' => "素材已存在"]);
        }


        $res = SnycFromGuang::create($params);

//        匹配库里的
        $map = ProductMap::where("guang_product_id", $params["guang_product_id"])->findOrEmpty();

        if (!$map->isEmpty()) {
            $flag = true;
            $data = [];
            $data["video_url"] = $params["url"];
            $data["uid"] = 1;
            $data["cover"] = $params["cover"];
            $data["status"] = 1;
            $data["title"] = $params["title"];
            $data["category"] = $params["category"];
            $data["gather_id"] = $res["id"];
            $data["jd_url"] = $map["jd_url"];
            $data["topic"] = $map["topic"];
            $data["tag"] = $map["tag"];
            $data["create_time"] = $res["create_time"];
            $data["valid_time"] = $res["create_time"];

//                带人设视频先不分配账号
            if (isset($params["model"])) {
                $data["model"] = $params["model"];
            } else {
                $accounts = Account::where('platform','京东')
                    ->where('category','like','%'.$params["category"].'%')
                    ->whereNull("remark")
                    ->select()->toArray();

                if (!empty($accounts)) {
                    $account_ids_arr = [];
                    foreach ($accounts as $li) {
                        $account_ids_arr[] = $li["id"];
                    }
                    $data["account_id"] = $account_ids_arr[array_rand($account_ids_arr)];

                } else {
                    $flag = false;
                }
            }

            if ($flag) {
                SnycDistribute::create($data);
                SnycFromGuang::where('id',$res["id"])
                    ->update(['status'=>'3', 'jd_url'=>$map['jd_url'], 'get_time'=>$res["create_time"]]);
            }



        }

        return writeJson(200, $res, '创建成功');
    }

    public function getSyncList(Request $request)
    {
        $params = $request->post();

        $yesterday = strtotime("-15 day");
        $yesterday15 = strtotime(date('Y-m-d 00:00:00', $yesterday));


        $pageSize = $params['pageSize'] ?? 50;
        $uid = LoginToken::getInstance()->getCurrentUid();
        $query = SnycDistribute::field("a.*,b.username")
            ->alias('a')->with('account')
            ->join('lin_user b', 'a.uid=b.id');
        if ($uid == 1) {
            if (!empty($params['username'])){
                $uuid = User::where('username',$params['username'])->find()['id'];
                $query = $query->where('a.uid',$uuid);
            }
        } else {
            $query = $query->where('a.uid',$uid);

        }

        if (!empty($params['status'])){
            $query = $query->where('a.status',$params['status']);
        }

        if (!empty($params['start_time']) && !empty($params['end_time'])){
            $query = $query->whereBetween('a.create_time',[$params['start_time'],$params['end_time']]);
        }

        $list = $query->where("valid_time", ">", date('Y-m-d H:i:s', $yesterday15))
            ->order("id desc")->paginate($pageSize);

        return writeJson(200, $list, '获取成功');

    }

    public function getOne(Request $request)
    {
        $yesterday = strtotime("-10 day");
        $yesterday15 = strtotime(date('Y-m-d 00:00:00', $yesterday));

        $sync = SnycFromGuang::where('status',1)
            ->where(function($q) {
                $ten_min_ago = date("Y-m-d H:i:s", time() - 600);
                $q->whereNull('get_time')->whereor('get_time', '<', $ten_min_ago);
            })
            ->where("create_time", ">",  date('Y-m-d H:i:s', $yesterday15))
            ->whereNull("model")
            ->orderRand()->findOrEmpty();

        if(empty($sync)){
            return writeJson(400, [], '暂无素材');
        }
        $update_list = SnycFromGuang::where('guang_product_id',$sync['guang_product_id'])
            ->where('status',1)
            ->where(function($q) {
                $ten_min_ago = date("Y-m-d H:i:s", time() - 600);
                $q->whereNull('get_time')->whereor('get_time', '<', $ten_min_ago);
            })
            ->where("create_time", ">",  date('Y-m-d H:i:s', $yesterday15))
            ->whereNull("model")
            ->limit(70)->select();

        $ids_arr = [];
        foreach ($update_list as $li) {
            $ids_arr[] = $li["id"];
        }
        $ids_str = implode(',', $ids_arr);

        SnycFromGuang::whereIn('id',$ids_str)->update(['get_time'=>date('Y-m-d H:i:s')]);

        $sync["ids"] = $ids_str;
        return writeJson(200, $sync, '获取成功');
    }

    /**
     * 逛逛素材拉取
     * @param Request $request
     * @return \think\response\Json
     */
    public function pullFromGuang(Request $request)
    {


    }


    public function createDistribute(Request $request)
    {
        $params = $request->post();
        $ids_arr = explode(",", $params["ids"]);

        //排除高并发
        $sync = SnycFromGuang::where('id',$params['id'])->find();
        if ($sync['status'] == 3) {
            return writeJson(400, [], '已添加分发');
        }
        if ($sync['status'] == 4) {
            return writeJson(400, [], '素材已废弃');
        }
        if (empty($params['jd_url'])) {
            $res = SnycFromGuang::whereIn('id', $params["ids"])->update(['status'=>'4','reason'=>'没有找到京东链接']);
            return writeJson(200, $res, '素材废弃成功');
        }
        $uid = LoginToken::getInstance()->getCurrentUid();

        $category = $params['category'];
        $list = Account::where('platform','京东')
            ->where('category','like','%'.$category.'%')
//            ->where("uid", $uid)
//            ->where("id", ">=", 33)
            ->whereNull("remark")
            ->select()->toArray();
        if (empty($list)) {
            return writeJson(400, [], '分类账户不存在');
        }
        $account_ids_arr = [];
        foreach ($list as $li) {
            $account_ids_arr[] = $li["id"];
        }

        foreach ($ids_arr as $iid) {
            $gather = SnycFromGuang::field("title,url,cover,category,create_time")->where("id", $iid)->findOrEmpty();
            $data = [
                'title'=>$gather['title'],
                'video_url'=>$gather['url'],
                'uid'=>$uid,
                'cover'=>$gather['cover'],
                'category'=>$gather['category'],
                'gather_id'=>$iid,
                'jd_url'=>$params['jd_url'],
                'topic' => $params['topic'],
                'tag' => $params['tag'],
                'account_id' => $account_ids_arr[array_rand($account_ids_arr)],
                'status'=>1,
                'create_time'=>date('Y-m-d H:i:s'),
                'valid_time'=>$gather["create_time"]
            ];
            SnycFromGuang::where('id',$iid)->update(['status'=>'3', 'jd_url'=>$params['jd_url']]);
            $res = SnycDistribute::create($data);
        }

//        $start = date('Y-m-d 00:00:00');
//        $end = date('Y-m-d 23:59:59');
//
//        $userd_account_id = SnycDistribute::field("account_id,count(*) as count")
//            ->whereIn("account_id", $account_ids_arr)
//            ->where('create_time','>=',$start)
//            ->where('create_time','<=',$end)
//            ->group("account_id")
//            ->having("count < 75")
//            ->select()->toArray();
//        foreach ($account_ids_arr as $li) {
//            $flag = 0;
//            foreach ($userd_account_id as $lii) {
//                if ($li == $lii['account_id']) {
//                    $flag = 1;
//                    break;
//                }
//            }
//            if ($flag == 0) {
//                $userd_account_id[] = ['account_id'=>$li,'count'=>0];
//            }
//        }
//        foreach ($ids_arr as $iid) {
//            $gather = SnycFromGuang::field("title,url,cover")->where("id", $iid)->findOrEmpty();
//            shuffle($userd_account_id);
//            foreach ($userd_account_id as $a => $b) {
//                if ($b['count'] < 75) {
//                    $data = [
//                        'title'=>$gather['title'],
//                        'video_url'=>$gather['url'],
//                        'uid'=>$uid,
//                        'cover'=>$gather['cover'],
//                        'gather_id'=>$iid,
//                        'jd_url'=>$params['jd_url'],
//                        'topic' => $params['topic'],
//                        'tag' => $params['tag'],
//                        'account_id' => $b['account_id'],
//                        'status'=>1,
//                        'create_time'=>date('Y-m-d H:i:s')
//                    ];
//                    $userd_account_id[$a]['count']++;
//                    break;
//                }
//            }
//            SnycFromGuang::where('id',$iid)->update(['status'=>'3', 'jd_url'=>$params['jd_url']]);
//            $res = SnycDistribute::create($data);
//        }

        return writeJson(200, $res, '添加分发成功');
    }

    public function getDistribute(Request $request)
    {
//        return writeJson(200, [], '成功');
        $params = $request->get();

        $all_accounts = [];
        $ok_accounts = Account::field('id')->whereNull('remark')
            ->where("id", ">=", $params["start"])
            ->where("id", "<=", $params["end"])
            ->select()->toArray();
        foreach ($ok_accounts as $ac) {
            $all_accounts[] = $ac["id"];
        }
//        $accounts = SnycDistribute::field("account_id,count(*) as count")
//            ->where("save_time", ">", date('Y-m-d 00:00:00'))
//            ->group("account_id")
//            ->having("count >= 75")
//            ->select()->toArray();
//
//        $used_account = [];
//        foreach ($accounts as $ac) {
//            $used_account[] = $ac["account_id"];
//        }

//        $array_diff = array_diff($all_accounts, $used_account);
//        5分钟内用过的不发
        $get_time = date("Y-m-d H:i:s", time() - 480);
        $get_time_account_id = SnycDistribute::field("account_id")
            ->where("get_time",'>',$get_time)
            ->where("account_id", ">=", $params["start"])
            ->where("account_id", "<=", $params["end"])
            ->distinct(true)
            ->select()->toArray();

        $get_time_account = [];
        foreach ($get_time_account_id as $ac) {
            $get_time_account[] = $ac["account_id"];
        }

        $array_diff_diff = array_diff($all_accounts, $get_time_account);

//        剩余篇数为0的不发
        $full_account_id = Account::field("id")
            ->where("num", 0)
            ->where("get_time", ">", date("Y-m-d 00:00:00", time()))
            ->where("id", ">=", $params["start"])
            ->where("id", "<=", $params["end"])
            ->select()->toArray();

        $full_account = [];
        foreach ($full_account_id as $ac) {
            $full_account[] = $ac["id"];
        }

        $array_diff_diff_diff = array_diff($array_diff_diff, $full_account);
//        $rand_account_key = array_rand($array_diff_diff_diff);

        $yesterday = strtotime("-15 day");
        $yesterday15 = strtotime(date('Y-m-d 00:00:00', $yesterday));

        $query = SnycDistribute::with("account")
            ->where('status',1)
//            ->where("account_id", ">",  35)
//            ->where("create_time", ">",  "2024-11-21 12:00:00")
//            ->where("valid_time", ">", date('Y-m-d H:i:s', $yesterday15))
            ->where("valid_time", ">", '2025-01-15 00:00:00')
            ->whereIn("account_id", $array_diff_diff_diff);

        if (isset($params["title"])) {
            $query = $query->whereLike("title", "%".$params["title"]."%");
        }
//        $sync = $query->fetchSql()->select();
//        dump($sync);die;
        $sync = $query->orderRand()->findOrEmpty();

        if($sync->isEmpty()){
            return writeJson(400, [], '暂无分发数据');
        }
        SnycDistribute::where('id',$sync['id'])->update(['status'=>'2', 'get_time'=>Date("y-m-d H:i:s")]);

        return writeJson(200, $sync, '获取成功');
    }

    public function exportDistribute(Request $request)
    {
        return writeJson(200, [], '获取成功');

        $all_accounts = [];
        $event = new SnycDistribute();
        $ok_accounts = Account::field('id')
            ->where(function($q) {
                $q->whereNull('remark')->whereOr("remark", "");
            })
//            ->whereNull('remark')
            ->select()->toArray();
        foreach ($ok_accounts as $ac) {
            $all_accounts[] = $ac["id"];
        }
        $accounts = $event->field("account_id,count(*) as count")
            ->where("get_time", ">", date('Y-m-d 00:00:00'))
            ->group("account_id")
            ->having("count >= 30")
            ->select()->toArray();

        $used_account = [];
        foreach ($accounts as $ac) {
            $used_account[] = $ac["account_id"];
        }

        $array_diff = array_diff($all_accounts, $used_account);

        $yesterday = strtotime("-15 day");
        $yesterday15 = strtotime(date('Y-m-d 00:00:00', $yesterday));

        $sync = $event->field("a.id,title,video_url,cover,a.category,jd_url,topic,name,tag")
            ->alias('a')
            ->join("account ac", "a.account_id=ac.id")
            ->where('status',1)
            ->where("a.create_time", ">",  date('Y-m-d H:i:s', $yesterday15))
            ->whereIn("account_id", $array_diff)
//            ->whereIn("account_id", [1,7,12,34,81])
            ->whereNotLike("tag", "其他")
            ->group("account_id")
            ->select()->toArray();

        if(empty($sync)){
            return writeJson(400, [], '暂无分发数据');
        }
        $savedata = [];
        foreach ($sync as $s) {
            $item = [];
            $item["id"] = $s["id"];
            $item["status"] = 2;
            $item["get_time"] = Date("y-m-d H:i:s");
            $savedata[] = $item;
        }
        $event->saveAll($savedata);


        return writeJson(200, $sync, '获取成功');
    }

    public function importDistribute(Request $request)
    {
        $file = $request->file('file');
        $event = new SnycDistribute();

        $filename  = $file->getInfo()['tmp_name'];
        //设置excel格式
        $reader = PHPExcel_IOFactory::createReader('Excel2007');
        //载⼊excel⽂件
        $excel = $reader->load($filename);
        //读取第⼀张表
        $sheet = $excel->getSheet(0);
        //获取总⾏数
        $row_num = $sheet->getHighestRow();
        //获取总列数
        $col_num = 'J';
        $datas = []; //数组形式获取表格数据
        $head = [];

        for($row=1;$row<=$row_num;$row++)
        {
            for($col='A';$col<=$col_num;$col++) {
                if ($row == 1) {
                    $head[] = $sheet->getCell($col."1")->getValue();
                } else {
                    $datas[$row-2][] = $sheet->getCell($col.$row)->getValue();
                }
            }
        }

        $array2 = ["自定义分类", "发布账号", "标题", "描述", "视频路径", "视频封面", "封面图", "宝贝链接", "逛逛话题编号", "逛逛话题名称"];
        if (array_diff_assoc($head, $array2) != NULL) {
            throw new syncException(['msg' => '表结构不正确', 'error_code' => 20001]);
        }

        DB::startTrans();
        try {
            $cur_time = Date("y-m-d H:i:s");
            for ($row = 0; $row < count($datas); $row++) {
                $id = pathinfo($datas[$row][4], PATHINFO_FILENAME);

                $event->where("id", $id)->update(["status"=>3, "save_time"=>$cur_time]);
            }

            // 提交事务
            Db::commit();
        }catch (syncException $e) {
            // 回滚事务
            Db::rollback();
            throw new syncException([
                'msg' => '导表失败',
                'error_code' => 20002
            ]);
        }

        return writeJson(200, true, '回填成功');


    }

    public function updateDistribute(Request $request)
    {
        $params = $request->post();
        $update = [
            'status' => $params['status'],
            'nid' => empty($params['nid']) ? null : $params['nid'],
            'reason' => empty($params['reason']) ? null : $params['reason']
        ];
        if ($params["status"] == 3) {
            $update["save_time"] = date('Y-m-d H:i:s',time());
        }
        if ($params["status"] == 5) {
            $update["pub_time"] = date('Y-m-d H:i:s',time());
        }

        if ($params["status"] == 4 and ($params["reason"] == 'cookie过期' or $params["reason"] == '发满了')) {
            $update["status"] = 1;
        }

        $res = SnycDistribute::where('id',$params['id'])->update($update);

        if ($params["reason"] == "cookie过期") {
            Account::where("id", $params["account_id"])->update(["remark"=>"cookie过期"]);
        }

        return writeJson(200, $res, '更新成功');
    }


    public function upDistributeRemark(Request $request)
    {
        $params = $request->post();
        $params = json_decode($params['data'],true);
        foreach ($params as $n) {
            $update = [
                'remark_time' => date('Y-m-d H:i:s'),
                'pub_remark' => empty($n['pub_remark']) ? "" : $n['pub_remark']
            ];
            $res = SnycDistribute::where('nid',$n['nid'])->update($update);
        }

        return writeJson(200, $res, '更新成功');
    }

    public function getTitle($title)
    {
        if (empty($title)) {
            return writeJson(400, [], '标题不能为空');
        }
        $yiyan = new yiyan();
        $promot = "帮我改写十个字以内的话：".$title;
        $response = $yiyan->turbo($promot);
        return writeJson(200, $response, '获取成功');
    }

    public function getCopywriting($features)
    {
        if (empty($features)) {
            return writeJson(400, [], '特性不能为空');
        }
        $yiyan = new yiyan();
        $promot = "帮我根据文案内容:".$features.";写出适合短视频的种草文案四到五句话，返回内容只需要文案，不需要说明";
        $response = $yiyan->turbo($promot);
        return writeJson(200, $response, '获取成功');
    }

    public function getInfo(Request $request)
    {
        $params = $request->get();
        $info = SnycDistribute::where("nid", $params["id"])->findOrEmpty();

        return writeJson(200, $info, '获取成功');
    }

    public function getDraft()
    {
        $count = SnycDistribute::alias('s')
            ->field("a.account,account_id,count(*) as count")
            ->join('account a','a.id = s.account_id', 'left')
            ->where('status','3')
            ->group("account_id")
            ->select()->toArray();
        return writeJson(200, $count, '获取成功');
    }

    //创作者名称	创作者ID	生产人员	待分发	今日存草稿	发布数

    public function todayData()
    {
        $dis = new SnycDistribute();
        $account = Account::field("id,name,app_id")
            ->whereNull("remark")
            ->select()->toArray();

        $list = [];
        foreach ($account as $ac) {
            $item = [];
            $item["account_id"] = $ac["id"];
            $item["app_id"] = $ac["app_id"];
            $item["name"] = $ac["name"];
            $item["status5"] = 0;
            $item["status3"] = 0;
            $item["status1"] = 0;
            $list[] = $item;
        }

        $status3 = $dis->field("account_id,count(*) as count")
//            ->where("status", 3)
            ->where("save_time", ">=", date('Y-m-d 00:00:00',time()))
            ->group("account_id")
            ->select()->toArray();

        foreach ($list as $key=>$li) {
            foreach ($status3 as $k=>$s3) {
                if ($li["account_id"] == $s3["account_id"]) {
                    $list[$key]["status3"] = $s3["count"];
                    unset($status3[$k]);
                    break;
                }
            }
        }

        $status5 = $dis->field("account_id,count(*) as count")
            ->where("status", 5)
            ->where("pub_time", ">=", date('Y-m-d 00:00:00',time()))
            ->group("account_id")
            ->select()->toArray();

        foreach ($list as $key=>$li) {
            foreach ($status5 as $k=>$s5) {
                if ($li["account_id"] == $s5["account_id"]) {
                    $list[$key]["status5"] = $s5["count"];
                    unset($status5[$k]);
                    break;
                }
            }
        }
        $yesterday = strtotime("-15 day");
        $yesterday15 = strtotime(date('Y-m-d 00:00:00', $yesterday));

        $status1 = $dis->field("account_id,count(*) as count")
            ->where("status", 1)
            ->where("valid_time", ">", date('Y-m-d H:i:s', $yesterday15))
            ->group("account_id")
            ->select()->toArray();

        foreach ($list as $key=>$li) {
            foreach ($status1 as $k=>$s1) {
                if ($li["account_id"] == $s1["account_id"]) {
                    $list[$key]["status1"] = $s1["count"];
                    unset($status1[$k]);
                    break;
                }
            }
        }

        return writeJson(200, $list, '获取成功');

    }
}